using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using DTOLayer;

namespace BUSLayer
{
   public class ThamSoDAO
    {
       public static void ThemThamSo(ThamSoDTO ts)
       {
           //B1&B2 Tao chuoi ket noi va ket noi bang doi tuong ket noi
           OleDbConnection cn;
           cn = Database.ConnectionData();
           //B3 Tao chuoi strSQL ket noi CSDL
           string strSQL = "";
         
           strSQL = "Insert Into THAMSO(MaThamSo,SoTienGoiToiThieu,SoTienGoiThemToiThieu,ThoiHanToiThieuTinhLai) Values(?,?,?,?)";
           //B4 Thuc Thi chuoi strSQL
           OleDbCommand cmd = new OleDbCommand(strSQL, cn);

           cmd.Parameters.Add("@MaThamSo", OleDbType.LongVarChar);
           cmd.Parameters.Add("@SoTienGoiToiThieu", OleDbType.Double);
           cmd.Parameters.Add("@SoTienGoiThemToiThieu", OleDbType.Double);
           cmd.Parameters.Add("@ThoiHanToiThieuTinhLai", OleDbType.LongVarChar);


           cmd.Parameters[0].Value = ts.MaThanSo;
           cmd.Parameters[1].Value = ts.SoTienGoiToiThieu;
           cmd.Parameters[2].Value = ts.SoTienGoiThemToiThieu;
           cmd.Parameters[3].Value = ts.ThoiHanToiThieuTinhLai;
         

           cmd.ExecuteNonQuery();

           //B5Dong ket noi
           cn.Close();
       }
       public static void DeleteThamSoTheoMaThamSo(long mts)
       {
           //B1 &B2 Tao chuoi ket noi va mo doi tuong ket noi bang doi tuong ket noi
           OleDbConnection cn;
           cn = Database.ConnectionData();
           //B2 Tao chuoi strSQL Thao Tac CSDL
           string strSQl = "";
           strSQl = "Delete From THAMSO Where MaThamSo= ?";
           //B4 Thuc thi chuoi strSQL
           OleDbCommand cmd = new OleDbCommand(strSQl, cn);

           cmd.Parameters.Add("@MaThamSo", OleDbType.LongVarChar);
           cmd.Parameters["@MaThamSo"].Value = mts;
           cmd.ExecuteNonQuery();

           //B5 dong ket noi
           cn.Close();
       }
       public static void UpdateThamSo(ThamSoDTO ts)
       {
           //B1&B2 Tao chuoi ket noi va mo ket noi bang doi tuong ket noi
           OleDbConnection cn;
           cn = Database.ConnectionData();
           //B3 Tao chuoi strSQL thao tac CSDL
           string strSQL;

           strSQL = "UPDATE THAMSO Set SoTienGoiToiThieu=? ,SoTienGoiThemToiThieu=? ,ThoiHanToiThieuTinhLai=?  Where MaThamSo=?";
           
           //B4 Thuc Thi chuoi strSQL
           OleDbCommand cmd = new OleDbCommand(strSQL, cn);

           cmd.Parameters.Add("@SoTienGoiToiThieu", OleDbType.Double);
           cmd.Parameters.Add("@SoTienGoiThemToiThieu", OleDbType.Double);
           cmd.Parameters.Add("@ThoiHanToiThieuTinhLai", OleDbType.LongVarChar);
           cmd.Parameters.Add("@MaThamSo",OleDbType.LongVarChar);


           cmd.Parameters[0].Value = ts.SoTienGoiToiThieu;
           cmd.Parameters[1].Value = ts.SoTienGoiThemToiThieu;
           cmd.Parameters[2].Value = ts.ThoiHanToiThieuTinhLai;
           cmd.Parameters[3].Value = ts.MaThanSo;

           cmd.ExecuteNonQuery();
           //B5 Dong ket noi
           cn.Close();

       }
       public static long MaxMaLoaiTietKiem()
       {
           //B1&B2 tao chuoi kiet noi
           // OleDbConnection cn;
           // cn = Database.ConnectionData();
           //B3 Tao chuoi ket noi CSDL
           string sql = "SELECT  Max(MaThamSo) From THAMSO";
           //B4 thuc thi chuoi sql
           // OleDbCommand cmd = new OleDbCommand(sql, cn);
           long kq;
           // kq= long.Parse( cmd.ExecuteScalar().ToString());
           //cn.Close();

           kq = Database.ExecuteScalar(sql);
           return kq;

       }
       public static List<ThamSoDTO> LayAllDSThamSoRaList()
       {


           List<ThamSoDTO> list = new List<ThamSoDTO>();
           //B1&B2 tao chuoi ket noi
           OleDbConnection cn;
           cn = Database.ConnectionData();
           //B3 tao chuoi ket noi CSDL
           string sql = "SELECT * From THAMSO";
           //b4 thuc thi chuoi ket noi sql
           OleDbCommand cmd = new OleDbCommand(sql, cn);
           OleDbDataReader dr;
           dr = cmd.ExecuteReader();
           while (dr.Read())
           {
               ThamSoDTO ts = new ThamSoDTO();
               ts.MaThanSo = long.Parse(dr["MaThamSo"].ToString());
               ts.SoTienGoiToiThieu = (double)dr["SoTienGoiToiThieu"];
               ts.SoTienGoiThemToiThieu = (double)dr["SoTienGoiThemToiThieu"];
               ts.ThoiHanToiThieuTinhLai = long.Parse(dr["ThoiHanToiThieuTinhLai"].ToString());

               list.Add(ts);

           }
           cn.Close();
           return list;
       }
    }
}
